/*
 * DatabaseUtil.java
 */

package org.owasp.scrubbr.util;

import org.owasp.scrubbr.*;
import org.owasp.scrubbr.db.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DatabaseUtil {
    
    
    public static final int MYSQL = 0;
    public static final int MSSQL = 1;
    public static final int ORACLE = 2;
    
    public static Database DBORACLE = new Database("admin", 1521, "oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@%s:%d:%s", "select table_name as t from all_tables where owner not in ('SYS','SYSTEM','DBSNMP','OUTLN','MDSYS', 'CTXSYS', 'WKPROXY', 'PERFSTAT', 'DSSYS', 'ORDSYS', 'ORDPLUGINS', 'WKSYS','WMSYS', 'XDB', 'ANONYMOUS', 'ODM', 'ODM_MTR', 'OLAPSYS', 'TRACESVR', 'REPADMIN' )", "SELECT COLUMN_NAME as c,  FROM ALL_TAB_COLUMNS WHERE table_name= ? ORDER BY COLUMN_NAME ASC;", "\"", "\"");
    public static Database DBMYSQL = new Database("root", 3306, "com.mysql.jdbc.Driver", "jdbc:mysql://%s:%d/%s", "SELECT table_name as t FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ? ORDER BY table_name ASC", "SELECT column_name as c,  FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? and table_name= ? ORDER BY column_name ASC", "`", "`");
    public static Database DBMSSQL = new Database("sa", 1433, "com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://%s:%d;DatabaseName=%s", "SELECT table_name as t FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ORDER BY table_name ASC","","[","]");
    
    public static int DATABASE_TYPE = -1;
    
    public static int USER_TYPE_NORMAL = 0;
    public static int USER_TYPE_DBA = 1;
    public static int DATABASE_USER_TYPE = USER_TYPE_DBA;
    
    public static final String targetColumnTypes[] = {
        "varchar",
        "text"
    };

    public static String DATABASE_USER;
    public static String DATABASE_HOST;
    public static String DATABASE_PW;
    public static int DATABASE_PORT;
    public static String DATABASE_DB;
    
    /** Meant to be accessed statically */
    private DatabaseUtil() { }
    
    public static Connection getConnection() throws ConnectionException {
        
        /*
         * Load the right driver into memory.
         */
        
        String driverToLoad = null;
        String connectionString = null;
        
        try {
            
            switch(DATABASE_TYPE) {
                case ORACLE:
                    driverToLoad = DBORACLE.getDriverClassName();
                    connectionString = DBORACLE.getConnectionString();
                    break;
                case MSSQL:
                    driverToLoad = DBMSSQL.getDriverClassName();
                    connectionString = DBMSSQL.getConnectionString();
                    break;
                case MYSQL:
                    driverToLoad = DBMYSQL.getDriverClassName();
                    connectionString = DBMYSQL.getConnectionString();
                    break;
            }
            
            connectionString = String.format(connectionString, new Object[] { DATABASE_HOST, Integer.valueOf(DATABASE_PORT), DATABASE_DB } );
            
            //System.out.println(connectionString);
            
            Class.forName(driverToLoad);
            
        } catch (ClassNotFoundException cnfe) {
            throw new ConnectionException("Could not find class on classpath: " + driverToLoad);
        }

        try {
            
            if ( DATABASE_TYPE == ORACLE ) {
                
                    Properties props = new Properties();
                    props.put("user", DatabaseUtil.DATABASE_USER);
                    props.put("password", DatabaseUtil.DATABASE_PW);
                    
                    if ( DATABASE_USER_TYPE == USER_TYPE_DBA ) {
                        props.put("internal_logon", "sysdba");
                    }
                    
                    return DriverManager.getConnection(connectionString, props);
                    
            }
            //System.out.println(DatabaseUtil.DATABASE_USER + ":" + DatabaseUtil.DATABASE_PW);
            return DriverManager.getConnection(connectionString, DatabaseUtil.DATABASE_USER, DatabaseUtil.DATABASE_PW);            
            
        } catch (SQLException ex) {
            throw new ConnectionException(ex);
        }
        
    }

    
    public static List<String> getTableColumns(Connection conn, String table) throws ConnectionException {
        
        ArrayList<String> columns  = new ArrayList<String>();
    
        PreparedStatement pstmt = null;
        ResultSet rs = null;
     
        try {

            switch (DATABASE_TYPE) {
                
                case ORACLE:
                    
                    pstmt = conn.prepareStatement(DBORACLE.getQueryColumns());
                    
                    //   pstmt.setString(1,DATABASE_DB);
                    //   pstmt.setString(2,table);
                    pstmt.setString(1,table);
                    
                    break;
                    
                case MYSQL:
                    
                    pstmt = conn.prepareStatement(DBMYSQL.getQueryColumns());
                    
                    pstmt.setString(1,DATABASE_DB);
                    pstmt.setString(2,table);
                    
                    break;
                    
                case MSSQL:
                    
                    pstmt = conn.prepareStatement(DBMSSQL.getQueryColumns());
                    
                    pstmt.setString(1,DATABASE_DB);
                    pstmt.setString(2,table);
                    
                    break;
                    
                default:
                    throw new SQLException("Database unsupported: " + DATABASE_TYPE);
            }

            rs = pstmt.executeQuery();
            
            while ( rs.next() ) {
                
                columns.add(rs.getString("c"));
            }
        
            return columns;            
            
        } catch (SQLException e) {
            
            throw new ConnectionException(e);
            
        } finally {
            
            safeClose(rs);
            safeClose(pstmt);
            
        }

    }
    
    public static List<String> getAllTables(Connection conn) throws ConnectionException {
        
        ArrayList<String> tables  = new ArrayList<String>();
    
        PreparedStatement pstmt = null;
        ResultSet rs = null;
     
        try {

            switch (DATABASE_TYPE) {
                case ORACLE:
                    pstmt = conn.prepareStatement(DBORACLE.getQueryTables());
                    break;
                case MYSQL:
                    pstmt = conn.prepareStatement(DBMYSQL.getQueryTables());
                    break;
                case MSSQL:
                    pstmt = conn.prepareStatement(DBMSSQL.getQueryTables());
                    break;
                default:
                    throw new SQLException("Database unsupported: " + DATABASE_TYPE);
            }
        
            switch (DATABASE_TYPE) {
                
                case ORACLE:
                    
                    //pstmt.setString(1,DATABASE_DB);
                    break;
                    
                case MYSQL:
                    
            pstmt.setString(1,DATABASE_DB);
                    break;
                    
                case MSSQL:
                    
                    //pstmt.setString(1,DATABASE_DB);
                    break;
            
                default:
                    throw new SQLException("Database unsupported: " + DATABASE_TYPE);
            }
            
            
            
            
            rs = pstmt.executeQuery();
            
            while ( rs.next() ) {
                
                tables.add(rs.getString("t"));
                
            }
            
            return tables;
            
        } catch (SQLException e) {
            
            throw new ConnectionException(e);
            
        } finally {
            
            safeClose(rs);
            safeClose(pstmt);
            
        }
   
    }

    public static void safeClose(Connection c) {
        if ( c != null ) {
            try {
               c.close();
            } catch(Exception e) {}
        }
    }
    
    public static void safeClose(ResultSet rs) {
        
        if ( rs != null ) {
            try {
                rs.close();
            } catch (Exception e) {}
        }
    }
    
    public static void safeClose(PreparedStatement ps) {
        
        if ( ps != null ) {
            try {
                ps.close();
            } catch (Exception e) {}
        }
    }

    
    public static int convertNameToType(String columnType) {

        String c;
        
        if ( columnType == null ) {
            return Types.NULL;
        }

        c = columnType.toLowerCase();

        if ( c.equalsIgnoreCase("tinyint") ) {
            return Types.TINYINT;
        } else if ( c.equalsIgnoreCase("int") ) {
            return Types.INTEGER;
        } else if ( c.equalsIgnoreCase("smallint") ) {
            return Types.SMALLINT;
        } else if ( c.equalsIgnoreCase("bigint") ) {
            return Types.BIGINT;
        } else if ( c.equalsIgnoreCase("bit") ) {
            return Types.BIT;
        } else if ( c.equalsIgnoreCase("decimal") ) {
            return Types.DECIMAL;
        } else if ( c.equalsIgnoreCase("numeric") ) {
            return Types.NUMERIC;
        } else if ( c.equalsIgnoreCase("money") ) {
            return Types.BIGINT;
        } else if ( c.equalsIgnoreCase("smallmoney" )) {
            return Types.INTEGER;
        } else if ( c.equalsIgnoreCase("float") ) {
            return Types.FLOAT;
        } else if ( c.equalsIgnoreCase("real") ) {
            return Types.REAL;
        } else if ( c.equalsIgnoreCase("datetime") || c.equalsIgnoreCase("smalldatetime" )) {
            return Types.TIMESTAMP;
        } else if ( c.equalsIgnoreCase("char") ) {
            return Types.CHAR;
        } else if ( c.startsWith("varchar") ) {
            return Types.VARCHAR;
        } else if ( c.startsWith("text") ) {
            return Types.VARCHAR; // this needs to be addressed
        } else if ( c.equalsIgnoreCase("nchar") ) {
            return Types.NCHAR;
        } else if ( c.startsWith("nvarchar") ) {
            return Types.NVARCHAR;
        } else if ( c.startsWith("ntext") ) {
            return Types.NVARCHAR; // this needs to be addressed
        } else if ( c.equalsIgnoreCase("binary")) {
            return Types.BINARY;
        } else if ( c.equalsIgnoreCase("varbinary")) {
            return Types.VARBINARY;
        } else if ( c.equalsIgnoreCase("image") ) {
            return Types.BLOB;

        /* begin oracle diffs */
        } else if ( c.startsWith("number") ) {
            return Types.INTEGER;
        } else if ( c.equals("long") ) {
            return Types.LONGVARBINARY;
        } else if ( c.startsWith("timestamp" )) {
            return Types.TIMESTAMP;
        } else if ( c.equals("blob") ) {
            return Types.BLOB;
        } else if ( c.equals("nblob") ) {
            return Types.NCLOB;
        } else if ( c.equals("clob") ) {
            return Types.CLOB;
        }

        return Types.OTHER;
    }


}
